﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Department.App_Code;

namespace Department
{
    public partial class Details : System.Web.UI.Page
    {
        public Department.App_Code.Department Department { get; set; }
        private int depID;
        private int id;
        private int type;
        private SqlCommand cmd = null;
        private string sql = string.Empty;
        private SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DepartmentConnectionString"].ConnectionString);

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                ValidateParams();

                using (conn)
                {
                    try
                    {
                        conn.Open();
                        cmd = new SqlCommand(sql, conn);
                        cmd.Parameters.AddWithValue("@ID", id);
                        cmd.Parameters.AddWithValue("@DepID", depID);
                        cmd.Parameters.AddWithValue("@Type", type);
                        
                        switch (type)
                        {
                            case 1: //Lecturer Details
                                {
                                    sql = @"SELECT LecturersFromDB.DegreeName, LecturersFromDB.RankName, LecturersFromDB.LecturerFullNameBG AS Name, Lecturers.PictureLink,  Lecturers.Info, Lecturers.Office, Lecturers.Phone, Lecturers.Email, Lecturers.PersonalHomePage, LecturersFromDB.LecturerID FROM Lecturers RIGHT OUTER JOIN LecturersFromDB ON Lecturers.LecturerDbID = LecturersFromDB.LecturerID WHERE (LecturersFromDB.LecturerID=@ID) AND isnull(Lecturers.IsHidden,0)= 0 AND (LecturersFromDB.DepartmentID = @DepID) ORDER BY Name";
                                    cmd.CommandText = sql;
                                    SqlDataReader dr = cmd.ExecuteReader();
                                    RepeaterLecturers.DataSource = dr;
                                    if (dr.HasRows)
                                        DetailsPanel.Visible = true;
                                    RepeaterLecturers.DataBind();
                                    dr.Close();

                                    this.Title = "Преподаватели | Информационни технологии и комуникации";

                                } break;
                            case 2: //News Details
                                {
                                    sql = @"SELECT ID, Heading, Date, Text FROM News WHERE IsHidden = 0 AND ID=@ID AND (FacDeptID=@DepID)";
                                    cmd.CommandText = sql;
                                    SqlDataReader dr = cmd.ExecuteReader();
                                    RepeaterNews.DataSource = dr;
                                    RepeaterNews.DataBind();
                                    if (dr.HasRows)
                                        DetailsPanel.Visible = true;

                                    dr.Close();
                                    foreach (RepeaterItem repeaterItem in RepeaterNews.Items)
                                    {
                                        // SQL Select Command
                                        cmd.CommandText = @"SELECT D.Name AS DocName, D.Description AS DocDesc, D.Link FROM News INNER JOIN DocumentsForNews ON News.ID = DocumentsForNews.NewsID INNER JOIN Documents AS D ON DocumentsForNews.DocID = D.ID WHERE (D.IsHidden = 0) AND News.ID=@ID AND News.TypeID=@Type ORDER BY D.Date DESC";
                                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                                        DataSet ds = new DataSet();
                                        da.Fill(ds);

                                        // Databinding with Nested Repeater Control
                                        ((Repeater)(repeaterItem.FindControl("RepeaterDocForNews"))).DataSource = ds;
                                        ((Repeater)(repeaterItem.FindControl("RepeaterDocForNews"))).DataBind();
                                    }

                                    this.Title = "Новини | Информационни технологии и комуникации";
                                } break;
                            case 3: //Discipline Details
                                {
                                    sql = @"SELECT DisciplineFromDB.DisciplineName AS Name,  Disciplines.Description,  Disciplines.ID,  Disciplines.TypeID, SpecialtiesFromDB.DepartmentID
                                            FROM SpecialtiesFromDB INNER JOIN DisciplineFromDB ON SpecialtiesFromDB.SpecID = DisciplineFromDB.SpecID LEFT OUTER JOIN Disciplines
                                            ON DisciplineFromDB.DisciplineID = Disciplines.DisciplineDbID WHERE (SpecialtiesFromDB.DepartmentID = @DepID) AND (isnull(Disciplines.IsHidden,0)= 0) AND DisciplineFromDB.DisciplineID=@ID ORDER BY Name";
                                    cmd.CommandText = sql;
                                    SqlDataReader dr = cmd.ExecuteReader();
                                    RepeaterDisc.DataSource = dr;
                                    RepeaterDisc.DataBind();
                                    if (dr.HasRows)
                                        DetailsPanel.Visible = true;

                                    dr.Close();

                                    foreach (RepeaterItem repeaterItem in RepeaterDisc.Items)
                                    {
                                        // SQL Select Command
                                        cmd.CommandText = @"SELECT LecturersFromDB.LecturerFullNameBG AS LecturerName, Lecturers.ID AS LecturerID, Lecturers.TypeID AS Type FROM Disciplines AS D INNER JOIN LecturerDiscipline ON D.ID = LecturerDiscipline.DiscID 
                                            INNER JOIN Lecturers ON LecturerDiscipline.LecturerID = Lecturers.ID INNER JOIN LecturersFromDB ON Lecturers.LecturerDbID = LecturersFromDB.LecturerID WHERE D.ID=@ID AND D.TypeID=@Type";
                                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                                        DataSet ds = new DataSet();
                                        da.Fill(ds);

                                        // Databinding with Nested Repeater Control
                                        ((Repeater)(repeaterItem.FindControl("RepeaterDiscLecturer"))).DataSource = ds;
                                        ((Repeater)(repeaterItem.FindControl("RepeaterDiscLecturer"))).DataBind();
                                    }

                                    this.Title = "Дисциплини | Информационни технологии и комуникации";
                                } break;

                            case 4: //Event Details
                                {
                                    sql = @"SELECT ID, Name, EventDate, Description FROM Events WHERE IsHidden = 0 AND ID=@ID AND (FacDeptID=@DepID)";
                                    cmd.CommandText = sql;
                                    SqlDataReader dr = cmd.ExecuteReader();
                                    RepeaterEvent.DataSource = dr;
                                    RepeaterEvent.DataBind();
                                    if (dr.HasRows)
                                        DetailsPanel.Visible = true;
                                    dr.Close();

                                    foreach (RepeaterItem repeaterItem in RepeaterEvent.Items)
                                    {
                                        // SQL Select Command
                                        cmd.CommandText = @"SELECT D.Name AS DocName, D.Description AS DocDesc, D.Link FROM Events INNER JOIN DocumentsForEvents ON Events.ID = DocumentsForEvents.EventID INNER JOIN Documents AS D ON DocumentsForEvents.DocID = D.ID WHERE D.IsHidden = 0 AND Events.ID=@ID AND Events.TypeID=@Type ORDER BY D.Date DESC";
                                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                                        DataSet ds = new DataSet();
                                        da.Fill(ds);

                                        // Databinding with Nested Repeater Control
                                        ((Repeater)(repeaterItem.FindControl("RepeaterDocForEvent"))).DataSource = ds;
                                        ((Repeater)(repeaterItem.FindControl("RepeaterDocForEvent"))).DataBind();
                                    }

                                    this.Title = "Събития | Информационни технологии и комуникации";
                                } break;
                            case 5: //Educatoin Materials Details
                                {
                                    string discTitle = string.Empty; // The title of the selected discipline
                                    sql = @"SELECT D.DisciplineName AS Name FROM SpecialtiesFromDB AS s INNER JOIN DisciplineFromDB AS D ON s.SpecID = D.SpecID WHERE (s.DepartmentID = @DepID) AND (D.DisciplineID = @ID)";
                                    cmd.CommandText = sql;
                                    discTitle = Convert.ToString(cmd.ExecuteScalar());
                                    LabelDsicEdu.Text = discTitle;

                                    sql = @"SELECT DISTINCT YEAR(EM.AcademicYear) AS year FROM EducationMaterial AS EM INNER JOIN
                                            DisciplineFromDB ON EM.DiscID = dbo.DisciplineFromDB.DisciplineID
                                            WHERE (DisciplineFromDB.DisciplineID = @ID) AND (EM.IsHidden = 0) ORDER BY year DESC";
                                    cmd.CommandText = sql;
                                    SqlDataReader dr = cmd.ExecuteReader();
                                    RepeaterEduYear.DataSource = dr;
                                    RepeaterEduYear.DataBind();
                                    if (dr.HasRows)
                                        DetailsPanel.Visible = true;
                                    dr.Close();

                                    this.Title = "Учебни материали | Информационни технологии и комуникации";
                                } break;
                            default:
                                {
                                    sql = @"SELECT ID, Name, Text, Date FROM DynamicContent WHERE (IsHidden = 0) AND (ID=@ID) AND (FacDeptID=@DepID)";
                                    cmd.CommandText = sql;
                                    SqlDataReader dr = cmd.ExecuteReader();
                                    RepeaterDynamicContent.DataSource = dr;
                                    if (dr.HasRows)
                                        DetailsPanel.Visible = true;
                                    RepeaterDynamicContent.DataBind();

                                    dr.Close();
                                    foreach (RepeaterItem repeaterItem in RepeaterDynamicContent.Items)
                                    {
                                        cmd.CommandText = @"SELECT D.Name AS DocName, D.Description AS DocDesc, D.Link FROM DynamicContent INNER JOIN DocumentsForContent ON DynamicContent.ID = DocumentsForContent.DynContentID INNER JOIN Documents AS D ON DocumentsForContent.DocID = D.ID WHERE DynamicContent.ID=@ID AND DynamicContent.TypeID=@Type AND (D.IsHidden = 0) ORDER BY D.Date DESC";
                                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                                        DataSet ds = new DataSet();
                                        da.Fill(ds);

                                        // Databinding with Nested Repeater Control
                                        ((Repeater)(repeaterItem.FindControl("RepeaterDocForDynCnt"))).DataSource = ds;
                                        ((Repeater)(repeaterItem.FindControl("RepeaterDocForDynCnt"))).DataBind();
                                    }

                                    this.Title = "Други | Информационни технологии и комуникации";
                                }
                                break;
                        }
                    }
                    catch (SqlException ex)
                    {
                        string errorMessages = "";

                        for (int i = 0; i < ex.Errors.Count; i++)
                        {
                            errorMessages += "Index #" + i + "\n" +
                                             "Message: " + ex.Errors[i].Message + "\n" +
                                             "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                                             "Source: " + ex.Errors[i].Source + "\n" +
                                             "Procedure: " + ex.Errors[i].Procedure + "\n";
                        }

						//System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
						//log.Source = "My Application";
						//log.WriteEntry(errorMessages);
						Response.Write(errorMessages);
                    }

                    finally
                    {
                        if (cmd != null)
                            cmd.Dispose();
                        conn.Close();
                    }
                    if (!DetailsPanel.Visible)
                        NotFoundLabel.Visible = true;
                }
            }

        }
        protected void BtnEmType_Command(object sender, CommandEventArgs e)
        {
            DataSet ds = new DataSet();
            ValidateParams();
            sql = @"SELECT EM.Name, Doc.Link, Doc.Description AS DocDesc FROM Documents AS Doc INNER JOIN DocumentsToEduMaterials ON Doc.ID =  DocumentsToEduMaterials.DocID INNER JOIN EducationMaterial AS EM ON  DocumentsToEduMaterials.EduMaterialID = EM.ID 
                INNER JOIN EduMaterialType ON EM.EduMaterialTypeID =  EduMaterialType.ID INNER JOIN DisciplineFromDB ON EM.DiscID =  DisciplineFromDB.DisciplineID INNER JOIN
                SpecialtiesFromDB AS S ON DisciplineFromDB.SpecID = S.SpecID WHERE (EM.IsHidden = 0) AND (Doc.IsHidden = 0) AND (S.DepartmentID = @DepID) AND (DisciplineFromDB.DisciplineID = @ID) AND EduMaterialType.Name = @Name ORDER BY Doc.Date DESC";
            try
            {
                conn.Open();
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.Parameters.AddWithValue("@DepID", depID);
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = e.CommandArgument.ToString();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                foreach (RepeaterItem repeaterItem in RepeaterEduYear.Items)
                {
                    Repeater rep = ((Repeater)(repeaterItem.FindControl("RepeaterEduM")));
                    foreach (RepeaterItem repeater in rep.Items)
                    {
                        ((Repeater)(repeater.FindControl("RepeaterLinkEdu"))).DataSource = ds;
                        ((Repeater)(repeater.FindControl("RepeaterLinkEdu"))).DataBind();
                    }
                }
            }
            catch (SqlException)
            { }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Close();
            }
        }

        protected void BtnYear_Command(object sender, CommandEventArgs e)
        {
            DataSet ds = new DataSet();
            ValidateParams();
            sql = @"SELECT DISTINCT EduMaterialType.Name AS emType FROM EduMaterialType INNER JOIN EducationMaterial AS EM 
                    ON EduMaterialType.ID = EM.EduMaterialTypeID INNER JOIN DisciplineFromDB ON EM.DiscID = DisciplineFromDB.DisciplineID INNER JOIN
                    SpecialtiesFromDB AS S ON DisciplineFromDB.SpecID = S.SpecID WHERE (EM.IsHidden = 0) AND (S.DepartmentID = @DepID) 
                    AND (DisciplineFromDB.DisciplineID = @ID) AND YEAR(EM.AcademicYear) = @Year ORDER BY emType";
            try
            {
                conn.Open();
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.Parameters.AddWithValue("@DepID", depID);
                cmd.Parameters.Add("@Year", SqlDbType.NVarChar).Value = e.CommandArgument.ToString();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                foreach (RepeaterItem repeaterItem in RepeaterEduYear.Items)
                {
                    ((Repeater)(repeaterItem.FindControl("RepeaterEduM"))).DataSource = ds;
                    ((Repeater)(repeaterItem.FindControl("RepeaterEduM"))).DataBind();
                }
            }
            catch (SqlException)
            { }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Close();
            }
        }

        private void ValidateParams()
        {
            Department = DepartmentProperties.GetDepartment(Request);
            depID = Department.ID;
            try
            {
                //Validating URL parameters
                Convert.ToInt32(Request.Params["ID"]);
                Convert.ToInt32(Request.Params["Type"]);
            }
            catch (Exception ex) // Catch OverflowException and FormatException
            {
                Response.Redirect("/Default.aspx?id=" + PageProperties.GetMainPageID());
                ex.ToString();
            }
            id = Convert.ToInt32(Request.Params["ID"]);
            type = Convert.ToInt32(Request.Params["Type"]);
        }

    }
}